USE [BiSA]
GO

/****** Object:  View [dbo].[vwChrCompareSites]    Script Date: 09/04/2011 13:49:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER VIEW [dbo].[vwChrCompareSites]
AS
-- A is a refers to KB and B is User TF
		select b.UserTFSitesId, B.UserDataDetailId, 
		a.TFBindingSitesId, A.TFBindingSitesDetailsId, 
		tfd.CellLine, tfd.TFName, 
		A.Chr Chr_A, A.start Start_A, A.[end] End_A, (A.[end] - A.start)+1 TagSize_A, 
	B.Chr Chr_B, B.start Start_B, B.[end] End_B, (B.[end] - B.start)+1 TagSize_B,
    ABS((A.[end] + A.start)/2 - (B.[end] + B.start)/2) CentreDistance,
	bpOverlap = CASE 
			WHEN B.[end] <= A.[END] AND B.Start >=A.Start
				THEN (B.[End] - B.Start) + 1
			WHEN A.[end] <= B.[END] AND A.Start >=B.Start
				THEN (A.[End] - A.Start) + 1	
			WHEN B.[end] <= A.[END] AND B.Start <=A.Start
				THEN (B.[End] - A.Start) + 1 
			WHEN B.[end] >= A.[END] AND B.Start <=A.[end]
				THEN (A.[End] - B.Start)  + 1
			WHEN B.[end] >= A.[END] AND B.Start >=A.[end] 
				THEN (A.[End] - B.Start)  + 1
			END
			,
	bpRange = CASE
		WHEN B.Start>A.[End]
			THEN B.Start - A.[End]
		WHEN A.Start>B.[End]
			THEN A.Start - B.[End]
		END
		,
		tfd.Active Active_A
	from  TFbindingSites A inner join UserTFSites B on A.Chr=B.Chr
	 inner join TFBindingSitesDetails tfd on A.TFBindingSitesDetailsId=tfd.TFBindingSitesDetailsId
		where tfd.Active=1 



GO

